package util;

import pojoCla.NewsChnl;
import pojoCla.SysUser;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author zhazha
 * 用来管理栏目的工具类
 *
 */
public class ChnlUtil {
    DButil db;

    //获取所有的栏目  需要传进 第一个参数为 pageIndex 第二个为 pageSize
    public List<NewsChnl> getSumChnl(Integer...pageMsg){
        db = new DButil();
        List<NewsChnl> list = new ArrayList<>();
        try {
            //判断是否传进来 分页下标和分页显示个数 来分别查询
            if(pageMsg.length > 0){
                pageMsg[0] = (pageMsg[0] - 1) * pageMsg[1]; //首先设置从哪条数据开始查
            }
            String sql = pageMsg.length > 0 ?  "select * from news_chnl limit ?,?" : "select * from news_chnl";
            ResultSet query = pageMsg.length > 0 ? db.query(sql,pageMsg) : db.query(sql,pageMsg);
            while (query.next()){
              NewsChnl chnl = new NewsChnl();
              chnl.setId(query.getInt("id"));
              chnl.setCode(query.getString("code"));
              chnl.setName(query.getString("name"));
              chnl.setSort(query.getLong("sort"));
              list.add(chnl);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return list;
    }
    public List<NewsChnl> getSumChnlByUser(SysUser user){
        db = new DButil();
        List<NewsChnl> list = new ArrayList<>();
        //先根据用户查询出栏目代码
        String chnlCode = "";
        String sql = "select b.chnl_code from sys_user a join sys_user_chnl b on a.acct=b.acct where a.acct=?";
        Object[] obj = {user.getAcct()};
        try {
            ResultSet query = db.query(sql, obj);
            if(query.next()){
                chnlCode = query.getString(1);
            }
            //根据查询出得代码 分解拼接sql语句
            if(!chnlCode.isEmpty()){
                String[] split = chnlCode.split(",");
                StringBuilder sb = new StringBuilder("select * from news_chnl where code in (");
                for (int i = 0; i < split.length; i++) {
                    sb.append("?,");
                }
                sb.replace(sb.length()-1,sb.length(),")");
                ResultSet chnlAll = db.query(sb.toString(), split);
                while (chnlAll.next()){
                    NewsChnl chnl = new NewsChnl();
                    chnl.setId(chnlAll.getInt("id"));
                    chnl.setCode(chnlAll.getString("code"));
                    chnl.setName(chnlAll.getString("name"));
                    chnl.setSort(chnlAll.getInt("sort"));
                    list.add(chnl);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return list;
    }
    //计算出栏目的总页数
    public Integer getSumPage(int pageSize){
        db = new DButil();
        Integer sumPage = null;
        String sql = "select count(code) from news_chnl";
        try {
            ResultSet query = db.query(sql, new Object[]{});
            if(query.next()){
                sumPage = query.getInt(1);
            }
            sumPage = sumPage % pageSize == 0 ? sumPage / pageSize : sumPage / pageSize + 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return sumPage;
    }
    public boolean addChnl(String chnlNum,String chnlName){
        db = new DButil();
        String sql = "insert into news_chnl(code,name,crtime,uptime) values(?,?,?,?)";
        Timestamp ts = new Timestamp(new Date().getTime());
        Object[] obj = {chnlNum,chnlName,ts,ts};
        try {
            db.update(sql,obj);
            db.submitUpdate();
        } catch (Exception e) {
            try {
                db.getConn().rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return false;
        } finally {
            db.closeConn();
        }
        return true;
    }
    public boolean delChnl(String delChnlName){
        db = new DButil();
        NewsUtil nu = new NewsUtil();
        try {
            String[] split = delChnlName.split(",");
            String[] chnlCode = new String[split.length];
            StringBuilder sb = new StringBuilder("delete a.*,b.* from news_chnl a join news b on a.code=b.chnl_code where a.name in (");
            for (int i = 0; i < split.length; i++) {
                sb.append("?,");
            }
            sb.replace(sb.length()-1,sb.length(),")");
            db.update(sb.toString(),split);
            db.submitUpdate();
        } catch (Exception e) {
            try {
                db.getConn().rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return false;
        } finally {
            db.closeConn();
        }
        return true;
    }

    public boolean editChnl(String editChnlNum, String editChnlName,String editChnlID) {
        db = new DButil();
        String sql = "update news_chnl set sort=?,name=?,uptime=? where id=?";
        Timestamp ts = new Timestamp(new Date().getTime());
        Object[] obj = {editChnlNum,editChnlName,ts,editChnlID};
        try {
            db.update(sql,obj);
            db.submitUpdate();
        } catch (Exception e) {
            try {
                db.getConn().rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return false;
        }finally {
            db.closeConn();
        }
        return true;
    }
    public List<NewsChnl> selChnl(String selChnlName,int pageIndex,int pageSize){
        db = new DButil();
        List<NewsChnl> list = new ArrayList<>();
        String sql = "select * from news_chnl where name like ? limit ?,?";
        Object[] obj = {"%"+selChnlName+"%",(pageIndex - 1) * pageSize,pageSize};
        try {
            ResultSet query = db.query(sql, obj);
            while (query.next()){
                NewsChnl chnl = new NewsChnl();
                chnl.setId(query.getInt("id"));
                chnl.setCode(query.getString("code"));
                chnl.setName(query.getString("name"));
                chnl.setSort(query.getLong("sort"));
                list.add(chnl);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return list;
    }
    public Integer selChnlSumPage(String selChnlName,Integer pageSize){
        db = new DButil();
        Integer sumPage = null;
        String sql = "select count(*) from news_chnl where name like ?";
        Object[] ojb = {"%"+selChnlName+"%"};
        try {
            ResultSet query = db.query(sql, ojb);
            if(query.next()){
                sumPage = query.getInt(1);
            }
            sumPage = sumPage % pageSize == 0 ?  sumPage/pageSize : sumPage/pageSize + 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeConn();
        }
        return sumPage;
    }
    public String findChnlName(String chnlName){
        String name = "";
        db = new DButil();
        try {
            boolean isNum = true;

            char[] chars = chnlName.toCharArray();
            for (int i = 0; i < chars.length; i++) {
                if(!Character.isDigit(chars[i])){
                    isNum = false;
                    break;
                }
            }
            if(isNum){
                Integer integer = Integer.valueOf(chnlName);
                String sql = "select * from news_chnl where code=?";
                Object[] obj = {integer};
                ResultSet query = db.query(sql, obj);
                if(query.next()){
                    name = query.getString("name");
                }
            }else{
                String sql = "select * from news_chnl where name=?";
                Object[] obj = {chnlName};
                ResultSet query = db.query(sql, obj);
                if(query.next()){
                    name = query.getString("code");
                }
            }
        } catch (Exception e) {

        } finally {
            db.closeConn();
            return name;
        }
    }


}
